// -----------------------------------------------------------------------
// <copyright file="EmailAddressDB.cs" company="Imar.Spaanjaars.Com">
//   Copyright 2008 - 2009 - Imar.Spaanjaars.Com. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;

using Spaanjaars.ContactManager.BusinessEntities;
using System.Data.Common;
using Spaanjaars.Validation;

namespace Spaanjaars.ContactManager.Dal
{
	/// <summary>
	/// The EmailAddressDB class is responsible for interacting with the database to retrieve and store information
	/// about EmailAddress objects.
	/// </summary>
	public class EmailAddressDB
	{
		#region Public Methods

		/// <summary>Gets an instance of EmailAddress from the underlying datasource.</summary>
		/// <param name="id">The unique ID of the EmailAddress in the database.</param>
		/// <returns>An EmailAddress when the ID was found in the database, or null otherwise.</returns>
		public static EmailAddress GetItem(int id)
		{
			EmailAddress myEmailAddress = null;
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocEmailAddressSelectSingleItem", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					myCommand.Parameters.AddWithValue("@id", id);

					myConnection.Open();
					using (SqlDataReader myReader = myCommand.ExecuteReader())
					{
						if (myReader.Read())
						{
							myEmailAddress = FillDataRecord(myReader);
						}
						myReader.Close();
					}
				}
				myConnection.Close();
			}
			return myEmailAddress;
		}

		/// <summary>Returns a list with EmailAddress objects.</summary>
		/// <param name="contactPersonId">The ID of the ContactPerson for whom the e-mail addresses should be returned.</param>
		/// <returns>A EmailAddressCollection with the EmailAddress objects.</returns>
		public static EmailAddressCollection GetList(int contactPersonId)
		{
			return GetList(contactPersonId, -1, -1);
		}

		/// <summary>
		/// Returns a list with EmailAddress objects.
		/// </summary>
		/// <param name="contactPersonId">The ID of the ContactPerson for whom the e-mail addresses should be returned.</param>
		/// <param name="startRowIndex">The index of the first record to retrieve.</param>
		/// <param name="maximumRows">The maximum number of record to be returned.</param>
		/// <returns>
		/// A EmailAddressCollection with the EmailAddress objects.
		/// </returns>
		public static EmailAddressCollection GetList(int contactPersonId, int startRowIndex, int maximumRows)
		{
			EmailAddressCollection tempList = null;
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocEmailAddressSelectList", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					myCommand.Parameters.AddWithValue("@contactPersonId", contactPersonId);
					myCommand.Parameters.AddWithValue("@startRowIndex", startRowIndex);
					myCommand.Parameters.AddWithValue("@maximumRows", maximumRows);
					myConnection.Open();
					using (SqlDataReader myReader = myCommand.ExecuteReader())
					{
						if (myReader.HasRows)
						{
							tempList = new EmailAddressCollection();
							while (myReader.Read())
							{
								tempList.Add(FillDataRecord(myReader));
							}
						}
						myReader.Close();
					}
				}
			}
			return tempList;
		}

		/// <summary>Saves an instance of the <see cref="EmailAddress" /> in the database.</summary>
		/// <param name="myEmailAddress">The EmailAddress instance to save.</param>
		/// <returns>Returns true when the object was saved successfully, or false otherwise.</returns>
		public static int Save(EmailAddress myEmailAddress)
		{
			if (!myEmailAddress.Validate())
			{
				throw new InvalidSaveOperationException("Can't save a EmailAddress in an Invalid state. Make sure that IsValid() returns true before you call Save().");
			}

			int result = 0;
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocEmailAddressInsertUpdateSingleItem", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;

					myCommand.Parameters.AddWithValue("@email", myEmailAddress.Email);
					myCommand.Parameters.AddWithValue("@emailType", myEmailAddress.Type);
					myCommand.Parameters.AddWithValue("@contactPersonId", myEmailAddress.ContactPersonId);

					Helpers.SetSaveParameters(myCommand, myEmailAddress);

					myConnection.Open();
					int numberOfRecordsAffected = myCommand.ExecuteNonQuery();
					if (numberOfRecordsAffected == 0)
					{
						throw new DBConcurrencyException("Can't update e-mail address as it has been updated by someone else");
					}

					myEmailAddress.ConcurrencyId = Helpers.GetConcurrencyId(myCommand); ;
					result = Helpers.GetBusinessBaseId(myCommand);
				}
				myConnection.Close();
			}
			return result;
		}

		/// <summary>Deletes an e-mail address from the database.</summary>
		/// <param name="id">The ID of the EmailAddress to delete.</param>
		/// <returns>Returns <c>true</c> when the object was deleted successfully, or <c>false</c> otherwise.</returns>
		public static bool Delete(int id)
		{
			int result = 0;
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocEmailAddressDeleteSingleItem", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					myCommand.Parameters.AddWithValue("@id", id);
					myConnection.Open();
					result = myCommand.ExecuteNonQuery();
				}
				myConnection.Close();
			}
			return result > 0;
		}


		/// <summary>
		/// Returns the number of email addresses for a given <see cref="ContactPerson"/>.
		/// </summary>
		/// <param name="contactPersonId">The ID of the contact person.</param>
		public static int SelectCountForGetList(int contactPersonId)
		{
			using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
			{
				using (SqlCommand myCommand = new SqlCommand("sprocEmailAddressSelectList", myConnection))
				{
					myCommand.CommandType = CommandType.StoredProcedure;
					myCommand.Parameters.AddWithValue("@contactPersonId", contactPersonId);
					DbParameter idParam = myCommand.CreateParameter();
					idParam.DbType = DbType.Int32;
					idParam.Direction = ParameterDirection.InputOutput;
					idParam.ParameterName = "@recordCount";
					idParam.Value = 0;
					myCommand.Parameters.Add(idParam);
					myConnection.Open();
					myCommand.ExecuteNonQuery();
					myConnection.Close();
					return (int)myCommand.Parameters["@recordCount"].Value;
				}
			}
		}


		#endregion

		#region Private Methods

		/// <summary>
		/// Initializes a new instance of the EmailAddress class and fills it with the data fom the IDataRecord.
		/// </summary>
		private static EmailAddress FillDataRecord(IDataRecord myDataRecord)
		{
			EmailAddress myEmailAddress = new EmailAddress();
			myEmailAddress.Id = myDataRecord.GetInt32(myDataRecord.GetOrdinal("Id"));
			myEmailAddress.Email = myDataRecord.GetString(myDataRecord.GetOrdinal("Email"));
			myEmailAddress.Type = (ContactType)myDataRecord.GetInt32(myDataRecord.GetOrdinal("EmailType"));
			myEmailAddress.ContactPersonId = myDataRecord.GetInt32(myDataRecord.GetOrdinal("ContactPersonId"));
			myEmailAddress.ConcurrencyId = (byte[])myDataRecord.GetValue(myDataRecord.GetOrdinal("ConcurrencyId"));
			return myEmailAddress;
		}

		#endregion
	}
}